home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
The PC-SIG Library 10
/
The PC-Sig Library - Shareware for the IBM PC and Compatibles (PC-SIG)(Tenth Edition Disks 1-2804)(1991).iso
/
PC_SIGCD
/
06
/
3
/
DISK0637.ZIP
/
PART2
< prev
next >
Wrap
Text File
|
1986-03-01
|
47KB
|
1,278 lines
25
EDIT*FORECAST*SAVE*RETRIEVE*FOUR COLUMNS
Federal Tax Analysis and Spreadsheet
The original idea behind UNCLE was to enable a tax
consultant, accountant, or annuity agent to provide
quick answers to the questions most often
encountered in tax forecasting - questions
difficult, if not impossible, to answer with only
the aid of a calculator and mound of tables. The
three original functions planned were maximum
exclusion calculations, tax and Circular E
routines, and future values. The straightforward,
no-frills procedure that developed subsequently was
of great help, but the greatest need was to
interface these functions with the very purpose for
which they were originally intended - tax
forecasting! What was needed was a simple
spreadsheet that would quickly analyze and compare
a variety of tax situations, save them to diskette
if desired, and generate useful and comprehensive
illustrations as sales aids. The actual output that
is generated is adequate in many cases to make the
future work of true tax preparation a simple matter
of copying columns from the UNCLE spreadsheet.
The UNCLE spreadsheet enables a thorough but yet
simple analysis of raw data and if-then situations
to produce a usable financial plan for the taxable
year. A good annuity agent, armed with a copy of
UNCLE can not only hustle a ton of business, but he
can blow the socks off any client with the
thoroughness, clarity, and efficiency of his
service. As a matter of fact, he will find the
program pays for itself just in the speedy
evaluation of his own tax status, an otherwise
distasteful task now made fast, fun, and easy.
The tax analysis module has been designed for
maximum flexibility combined with maximum detail.
Hence, while several shortcuts enable the bypassing
of superfluous input, special enhancements can make
the analysis as in-depth as the user desires. He is
at liberty, after first entering the module, to
26
directly address a new spreadsheet, retrieve
another from diskette, directly edit the
spreadsheet, or to begin detailed input following
the format of the federal 1040 form. All input
remains in the module despite any detours the user
may take, and is politely offered as default input
should he desire to edit any of his work. The
spreadsheet itself is designed to offer slightly
condensed information so that significant
parameters can be simultaneously observed on a
single spreadsheet all at one time and parameters
avoided if they do not lend themselves to what-if.
There are only a few things that a user need know
prior to using the spreadsheet, most being
self-explanatory. If the analysis module is left
without saving the spreadsheet to diskette, then
all data is erased. This is the only way the data
will be erased unless there is a malfunction in the
hardware. It may be reassuring to do this from time
to time.
Information entered into the simulated 1040 screens
will not be part of the spreadsheet until the
spreadsheet is accessed via this route. When it
does become part of the spreadsheet then all
columns will contain the information. However,
until the material is edited by passing the cursor
through the fields, taxes and allowances will NOT
be calculated. The purpose of this is to force the
user to examine his data. Once this is done, the
information is saved as long as the tax analysis
module is not left. If the option to edit a
spreadsheet is taken, the data will yet be offered
as default, but the user will be again prompted for
name, filing status, and number of exemptions. This
signals to the user the spreadsheet currently in
use and provides a recheck of critical data.
Because data is desired for a number of possible
uses, and because the 1040 form is overly complex
for this purpose, information does not strictly
adhere to the 1040 format. For example:
27
1) Tips will be reported as other income rather
than included as part of line 7. Thus the
parts denoted as line 7 on the spreadsheet
refer to the fact that these amounts are
part of line 7, not necessarily equal to it;
however, the row marked line 32 should
correspond exactly to form 1040.
2) In order to deal effectively with
tax-sheltered annuities, incomes prior to
deferred reductions are shown in addition to
the salary which is included on line 7. The
total gross salary or wage is FICA wage,
excluding tips.
3) Alimony, whether paid or received, is
reported on a single line.
4) When entering itemized deductions, the zero
bracket amount is included in the 1040
input, but only net deductions are shown on
the spreadsheet.
UNCLE is designed to illustrate tax-savings due to
various investments, particulary TSA's and IRA's,
and how monthly income may be affected; however,
other investments can be used and space is provided
to list these. Any change in tax status throughout
the year can easily be compensated for by a new W-4
which is obtained by use of the spreadsheet.
28
Figure 10. Tax Analysis - Power at Your Fingertips
29
FEDERAL TAX ANALYSIS & SPREADSHEET
THE MODULE STEP BY STEP (Figure 10)
CHOOSE >>> (1) Enter data (form 1040)
A new spreadsheet may be begun without taking this
route, however this route has several advantages:
a) the input prompts are longer and more
explanatory.
b) many inputs, if not required to demonstrate
what-if situations, do not appear on the
spreadsheet, but they are taken into account if
input from this source first.
c) all inputs are written into the spreadsheet and
offered as default, eliminating the need to reenter
the same numbers over and over in each column.
d) use CAUTION - if the data is registered again
from the 1040 input it will cover over any changes
made previously - you will be reminded of this
possibility. An asterisk (*) indicates the input
will be re-offered for modification on the
spreadsheet columns.
Figure 11. Screen 1 - General Information.
30
INPUT >>>> AGENT'S NAME, CLIENT'S NAME, FILING
STATUS, NUMBER OF EXEMPTIONS
These should be self-explanatory. It is suggested
that the caps-lock remain set since single letter
string variables require caps (see Figure 11).
Figure 12. Data Entry Screen for Income.
INPUT >>>> CLIENT'S (SPOUSE'S) TOTAL FICA SALARY *
These values are not shown on the 1040 form, since
deferred reductions are not reportable as income;
however, include them here to prevent confusion in
31
the excludable limits and withholdings modules and
for purposes of illustration (see Figure 12).
INPUT >>>> ADDITIONAL SALARIES OR TIPS
Miscellaneous income from other sources not subject
to what-if analysis.
INPUT >>>> INTEREST INCOME *
Interest gained from any source other than pension
that is taxable.
INPUTS >>> TOTAL DIVIDEND INCOME, EXCLUDED DIVIDEND
INCOME, STATE AND LOCAL TAX REFUNDS.
For an explanation of these inputs refer to the
appropriate IRS publications.
INPUT >>>> INCOME FROM ALIMONY OR ALIMONY PAID
This is separated into into the two amounts on the
1040, but here use a positive value for income, a
negative for alimony paid.
INPUTS >>> CAPITAL GAINS AND TAXABLE PENSION INCOME
Refer to IRS publications.
INPUT >>>> RENTS, ROYALTIES, PARTNERSHIPS, ETC. *
Income received from these sources often represents
investments that need to be evaluated, therefore
the input is again offered on the spreadsheet - see
IRS publications for explanation.
32
INPUT >>>> FARM INCOME
See appropriate IRS publication.
INPUT >>>> ANY OTHER INCOME *
This could be anything - if other income types that
are not offered for modification on the spreadsheet
by rare circumstance need to be modified then put
them here and explain in the notes.
INPUTS >>> MOVING EXPENSE AND BUSINESS EXPENSES
For income adjustments refer to Figure 13.
INPUT >>>> COMBINED IRA CONTRIBUTIONS *
Combine client and spouse deductible IRA
contributions for the taxable year.
INPUT >>>> KEOGH CONTRIBUTIONS
If analysis of this is desired use another field
and reference in the notes.
INPUTS >>> CONTRIBUTIONS TO TSA'S AND DEFERRED *
RETIREMENTS - CLIENT AND SPOUSE.
These four inputs are again offered in the
spreadsheet. The TSA's are self-explanatory, the
deferred retirement refers to employer reductions
to salary, if tax-deferred.
INPUT >>>> PENALTIES ON EARLY SAVINGS WITHDRAWALS
Refer to IRS publications.
33
Figure 13. Adjustments to Income.
Figure 14. Data Entry for Deductions, Additional
Taxes, and Credits.
34
INPUT >>>> MARITAL DEDUCTION *
Refer to IRS publications; however, TSA
contributions may reduce this value, so exercise
caution. For deductions, etc. refer to Figure 14.
INPUT >>>> ITEMIZED DEDUCTIONS *
This amount is gross, not net, and thus must
include the zero bracket amount, while on the
spreadsheet only the net value is used. The purpose
of this is to remind the user that the zero bracket
must be subtracted from total deductions.
INPUT >>>> CHARITABLE CONTRIBUTIONS
This only appears if net itemized deductions is
less than or equal to zero. If the user enters more
than is allowable then the computer adjusts the
amount as necessary (see IRS publication).
INPUTS >>> ALL ADDITIONAL TAX INPUTS (*)
Refer to IRS publications if necessary. There is a
space reserved for miscellaneous additional taxes
that can be referenced in the notes.
INPUTS >>> (ALL TAX CREDITS) & BUSINESS CREDITS *
For a detailed explanation of these refer to IRS
publications. The last input for GENERAL BUSINESS &
OTHER CREDITS * is again presented on the
spreadsheet and allows for miscellaneous input that
can be referenced in the notes. As with charitable
contributions, the computer will adjust excess
amounts; however, the user must refer to IRS
publications to be aware of the procedures.
35
CHOICE >>> Edit spreadsheet
Use this option to go back and update, view, or
alter existing values on the sheet (see Figure 15).
Figure 15. The Spreadsheet in a Nutshell.
36
Figure 16. The Print Spreadsheet Option.
CHOICE >>> Print Spreadsheet (see Figure 16)
You may print as many columns as you wish,
beginning from the leftmost. You may also add up to
three lines of text as notes.
CHOICE >>> Save Spreadsheet
To save to the current drive, just enter a filename
- eight characters maximum. Use only alphabetical
characters. To save to a different drive, enter the
drive letter and a colon followed immediately by
the filename; for example, B:NAME. If you make an
error on this maneuver the computer will alert you
to the problem with a specific explanation and a
series of tones, except for an inappropriate drive
37
letter or filename, which will return the
appropriate error code.
All data held in the spreadsheet module is saved.
CHOICE >>> Retrieve Spreadsheet
This option operates the same as Save. NOTE: If the
retrieve was unsuccessful, it may be necessary to
return to the UNCLE MASTER MENU in order to clear
and close the nonexistent file in order to search
for another file.
CHOICE >>> Return to UNCLE MENU
Use this option when you are finished with the tax
analysis and spreadsheet module. CAUTION: Be sure
to save any work you want to keep, since leaving
the module erases the data. Likewise, it may be
reassuring (or necessary, in the case of an
unsuccessful retrieve) to leave the module and
return if done working with the present file;
however, a subsequent retrieve will overwrite any
current data.
THE UNCLE PRINTED REPORT:
Figure 17 demonstrates the report output. There is
no need to examine the report in detail, since most
of the values output correspond exactly to data
input by the user; however the last eight lines
should be mentioned.
OUTPUT >>> TOTAL MONTHLY INCOME
This is the monthly adjusted sum of gross salaries
from the paystub analysis.
insert figure 17: report output
40
OUTPUT >>> NET MONTHLY INCOME
The same as above less federal withholdings only.
OUTPUT >>> PERCENTAGE OF TAX DUE
This is the combined sum of total annual
withholdings from the paystub analysis as a
percentage of tax due projected by the spreadsheet.
No dollar figure is given to avoid cluttering the
report, but simply multiply this figure by the tax
due and divide by 100.
OUTPUT >>> MONTHLY INCOME CHANGE
For columns 2-4 only, this field displays any
change in NET income compared to the first column.
Use caution in interpreting the significance of
this value. Usually, a client will want to know the
immediate impact of any investment on his monthly
income.
To see any actual tax savings compare the TOTAL TAX
DUE values from the spreadsheet above.
OUTPUT >>> TSA'S, IRA'S, & OTHER INVESTMENTS
These show the most likely sources and amounts of
investments that went into the analysis.
41
APPENDIX A
Error Codes
The following codes are common to IBM basic
environments:
4 Out of data
5 Illegal function call
6 Overflow - a number too large
7 Out of memory - insufficient RAM to handle
combined arrays, data, and program workspace
at one time
14 Out of string space - the string input results
in an out of memory condition
15 String too long - an attempt was made to input
a string more than 255 characters long
16 String formula too complex - simplify the note
17 Can't continue - program does not exist, was
modified during execution, or another error
18 Undefined function - no function is assigned
to this function key
25 Device fault - printer not on or other fault
27 Out of paper - printer out of paper or not on
51 Internal error - an internal malfunction in
program execution, check condition of diskette
55 File already open - clear by leaving module
and returning
42
57 Device input/output error
62 Input past end - check to see if the file is a
valid UNCLE data file
64 Bad file name - invalid characters entered
67 Too many files - all directories on diskette
are full or the file specification is invalid
68 Device unavailable - attempt was made to open
a file to a non-existent device, check printer
connections
OTHER ERRORS: The most common, or likely errors,
will be accompanied by explicit messages on the
screen. If the error was related to save or
retrieve operation the user's attention will be
alerted by a series of tones.
43
APPENDIX B
Publication 571
This portion of the manual need not to be read
unless one desires a rather technical discussion of
the procedures used for calculating the limits.
Ordinarily, it would be omitted except that the
author feels responsible to those who might desire
to challenge the procedures used and require a
defense of them.
Unfortunately, the calculation of maximum
excludable limits is liable to frequent errors in
interpretation. The above described options are
valid options in the IRS code for 403(b) and
501(c), but are explained somewhat differently than
in Publication 571. Before addressing these
differences, however, it may be helpful to discuss
the percentage limits described in the code -
wherein the most common mistakes are made. For
example, where the code refers to a 25% of
compensation limit it actually means 25% of
includable compensation, the key word here being
INCLUDABLE. For a person earning $20,000 ordinary
salary his maximum limit is NOT $5,000, because if
he excluded the entire $5,000 his maximum would be
25% of $15,000 which is $3,750. This is really an
algebra problem. It actually turns out that 20%
ordinary salary is the true maximum and it is
unfortunate that Publication 571 does not make this
plain. The manual by Tarver and Martensen also
fails to use this much simpler approach to the 25%
application towards maximum limits. In the present
example the reader will find that 20% of gross
compensation is $4,000 and this will be the proper
figure. Note that this amount is also 25% of the
$16,000 compensation which would then be includable
as ordinary income. Do not use this example as a
basis for calculating a limit, since many other
factors also are important.
44
Another frequent error in the same calculation
deals with the disposition of salary reductions as
applied to employer-controlled plans. If such
amounts are to be taken into account and, for
example, 6% of gross salary is contributed, then
the 25% is NOT reduced to 19%, nor the 20% to 14%.
The approach is to take the 6% times the gross
salary, then subtract this from the gross salary,
then take 20% of the gross salary. This will always
work out to 20% x 94% = 18.8%, significantly more
than 16%! Where the OVERALL ELECTION is concerned,
additions to salary must be subtracted directly
from the limit as otherwise calculated.
It is important to mention that the options given
in Publication 571 are slightly different than the
ones given here. The main difference is in the idea
of "irrevocable election". It is very common for an
agent to use the any year's limitation election
(this is the same calculation as the single year's
option in UNCLE), but in later years to fall back
on the overall election. THIS IS NOT CORRECT
PROCEDURE, SINCE AMOUNTS LARGER THAN THE OVERALL
LIMITATION IN PRIOR YEARS NOW BECOME BACK TAXABLE.
Many agents scoff at the idea that the IRS would
ever involve itself to that degree of scrutiny, few
being a match for the math involved, but a good
many others take note of the fact that should the
IRS feel inclined to do so, then the client could
owe considerable back tax and penalty, and the
agent be liable for considerable damages. In fact,
any amounts excluded that are in excess of the
ordinary maximum exclusion allowance automatically
make an irrevocable election for the contributor
under any of the elections which would qualify the
amount taken. Some people are aware of the fact
that this applies to the any year's limition and
the overall limitation discussed in Publication
571, however, it is worthwhile to note that if it
was necessary to use either of the previous two
45
elections to defend prior TSA exclusions the use of
the retirement election disqualifies those amounts.
Again, most agents will probably err on this matter
and can open both themselves and their clients to
severe liability by using the retirement election
when exclusions in prior years may have exceeded
what was entitled under the level option.
It is necessary here to point out a fundamental
difference in emphasis between the UNCLE program
and Publication 571 and why it exists. While the
retirement option in UNCLE corresponds to the
retirement election and the single year's option to
the any year limitation, the level option in UNCLE
does not mean quite the same as the overall
limitation election. Use of the overall limitation
is binding and cannot be changed. A client may feel
that since the single year election may decline
each year if his contributions are large, he would
benefit using the overall limitation which will
remain (erroneously, remember) at 25% of includable
compensation. In actual practice this may not be
the case, since most teachers have several years
time in service and have unused limits in the past.
This means that the any year's limitation may
likely be much higher and can rather easily be
brought back to par if future reductions are not
large. Furthermore, by the time this election would
result in a lower limit than the overall limit, it
would likely be impossible using the latter to
build a TSA account up to the other's value, even
if he used the maximum each year until retirement.
UNCLE was designed to project maximum limits using
single year's maximums each year for a series of
years, so that the user can see this for himself.
46
UNCLE solves these problems by demonstrating the
level, i.e. period, option as well as the overall
election. Since a variation of the maximum
exclusion allowance for this option is superimposed
upon the overall limitation, it is impossible to
obtain an irrevocable election between a single
year's maximum and a chosen level amount. The
author did not make this recipe up on his own, but
in fact the calculations, while omitted in
Publication 571, have been in the code for a long
time.
The value of UNCLE lies in the fact that although a
good many worksheets abound that follow the
examples in Publication 571, only UNCLE calculates
and projects actual maximums, a trick far to
difficult for the average person, who must simply
check through the worksheet to see if the number
he's picked out of his hat will indeed work. Most
agents, in fact, use a straight 25% or even a
percentage plus $4,000, methods that expose them
and their clients to severe hazard.
The few, if any, successful agents who can really
understand these matters avoid attempting to
explain them to a client for obvious reasons. While
some agents are aware of this unhappy state, they
are at a loss to resolve the situation. In my own
experience, I have never met an agent who has a
strong enough math background (that includes the
author) to enable him to work the problem out in a
reasonable period of time. Unfortunately, while
most consider it unlikely the IRS will ever do a
thorough checkup on their clients because of this
complexity, they are playing a rather risky game.
UNCLE provides an answer to the dilemma. There is
an assumption here that desktop microcomputers will
soon be rather standard fare for life and health
brokers. Given that event, even considering the
47
present use of micros, UNCLE provides, in addition
to its other powerful functions, a quick and easy
method of calculating maximum excludable limits - a
procedure that to date has not been accomplished,
but which could make living a great deal easier for
a great many people.
A final word of caution is advised here. The
calculation of the maximum exclusion allowance and
its variations under the different options present
a more formidable task for the average person than
either Publication 571 or most company guidelines
present. The main problem lies in the fact that the
proper approach, like that of the 25% limitation
discussed above, is open to modification by the
term INCLUDABLE SALARY. To illustrate this point,
take the case of Bill Black on page 6 of
Publication 571. The exclusion allowance is
properly pegged at $33,600. This does not answer
the question that is begged most frequently - what
is the MAXIMUM exclusion allowance. I have before
me several guidelines for calculating the MAXIMUM
exclusion allowance published by major insurance
companies. Like Publication 571, they all fail to
calculate the maximum even though they calculate an
allowance correctly with the formula, given a
certain contribution which reduces includable
compensation. The pitiful person using these
guidelines can only guess at the maximum by trial
and error - perhaps never realizing that the figure
he uses must be subtracted from gross compensation,
or failing to notice that it exceeds the maximum
after spending hours experimenting.
But referring back to the case of Bill Black.
Please note that the $33,600 is the exclusion
allowance. The conclusion that this is the MAXIMUM
exclusion allowance is tragically misleading. If
the reader would simply attempt a figure of $12,000
48
to be excluded from income he will find that the
exclusion allowance is slightly reduced, but the
$12,000 is still within the limit. How then, may we
calculate the MAXIMUM exclusion allowance. Given
contributions to state retirement plans and other
forms of deffered compensation, this calculation
becomes far too complicated even for a
mathematician - provided he wants to double as an
agent. The lucky user of UNCLE may rest easy on the
matter!
49
APPENDIX C
Glossary for Maximum Excludable Limits
This glossary if provided as a special aid to
understanding parameters in the routine for
calculating tax sheltered annuity deferred limits.
Gross Annual Salary - excluding salary ADDITIONS by
employer for contribution to pension plans. Pension
contributions by the employee are included in this
gross whether deferred or not. All salary
reductions for the purpose of pension contributions
are defined in the program as employee
contributions even though at times referred to as
employer contributions by IRS code.
Employer's Current Contribution - ADDITIONS to
salary (not usually considered as salary) which are
contributed to a pension fund(s) during the taxable
year (always include these - they are important).
Employee's Current Contribution - REDUCTIONS from
gross salary contributed to a pension but only
insofar as they qualify for deferred tax status.
If these are not tax-deferred, omit them.
Past Year's Service - include the current year. For
periods of part-time employment, use decimal years
or convert fractional to decimal.
Employer's Total Previous Contributions - same
definition as for current, but this is the sum of
all prior contributions. For the retirement option
this is for the last ten year's through retirement
only (it may be easier to make a safe estimate).
Employee's Total Previous Contributions - same as
for current, but this is the sum of prior
contributions. For the retirement option this is
for the last ten year's through retirement only.
50
Other Plans - if past, these must include any
amounts for TSA contributions not previously
entered PLUS all other types of deferred
compensation except IRA's, if current exclude
TSA's.
Years Service Through Retirement - for the
retirement option. Use decimal years for part-time
or part years being careful to use actual dates of
employment (only ten or less will be counted).
Total Year's Service - for the level option only.
This is past years plus present and including
future anticipated period, usually through
retirement, but this need not be the case. Use
decimals.
LIMITED WARRANTY
UNCLE is provided solely as an aid for calculation
and illustration. Many of the calculations are
subject to errors in interpretation and many are
highly subjective in nature. In any event, the
entire risk is borne by the user. ARCON and the
author are not liable for any damages, incidental
or consequential, from the use of UNCLE software.
UNCLE is a software product copyrighted by ARCON.
Distribution and sale are only authorized by ARCON
subject to permission of the program author and for
single machine use only. Copying, duplicating, or
otherwise distributing UNCLE or the companion
manual, in part or in whole, is a violation of law.
When a user purchases a copy of UNCLE, he may
obtain limited servicing privileges by signing this
page, or a copy of it, and returning it to the
manufacturer. The rights so obtained will be:
1) Annual revisions or upgrades in response to
tax law changes at reduced rates.
2) Free replacement of materially defective
UNCLE diskettes within one year of
purchase.
NAME: ___________________________________________
ADDRESS: ________________________________________
________________________________________
________________________________________
LICENSE NUMBER: _________________________________
